﻿USE [NPMonitor]
GO

if exists(select name from sysobjects where name = 'vDailyReport' and type = 'V')
drop View vDailyReport
Go

CREATE VIEW vDailyReport
AS
SELECT  locationId,
		ScenarioNo,
		BeginTime,
		AVG([time]) AS AverageTime 
FROM 
(
	SELECT 
		PoolId AS locationId,
		scenariono,
		CAST((CONVERT(VARCHAR(13), BeginTimeFormat, 120) + ':00:00') AS DATETIME) AS BeginTime,
		[time]
	FROM 
		HttpActions WHERE ScenarioNO != ''
) T GROUP BY BeginTime,locationId,ScenarioNo

GO